Oracle
Summary
This document covers the information to gather from Oracle in order to configure a Qarbine data service. You can define multiple data services that access the same Oracle endpoint though with varying credentials. Once a data service is defined, you can manage which Qarbine principals have access to it and its associated data. A Qarbine administrator has visibility to all data services.
Oracle Configuration
Qarbine requires the following parameters to access the Oracle information:
- connection string,
- user, and
- password.
Qarbine uses the oracledb nodejs module to interact with Oracle. The connection string options are described at
https://node-oracledb.readthedocs.io/en/latest/user_guide/appendix_a.html#connection-strings
https://node-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html
Qarbine Configuration
Compute Node Preparation
Determine which compute node service endpoint you want to run this data access from. That URL will go into the Data Service’s Compute URL field. Its form is “https://domain:port/dispatch”. A sample is shown below.
The port number corresponds to a named service endpoint configured on the given target host. For example, the primary compute node usually is set to have a ‘main’ service. That service’s configuration is defined in the ˜./qarbine.service/config/service.main.json file. Inside that file the following driver entry is required
"drivers" :[
. . .
"./driver/oracleDriver.js"
]
The relevant configuration file name for non primary (main) Qarbine compute nodes is service.NAME.json. Remember to have well formed JSON syntax or a startup error is likely to occur. If you end up adding that entry then restart the service via the general command line syntax
pm2 restart <service>
For example,
pm2 restart main
or simply
pm2 restart all
Data Service Definition
Open the Administration Tool.
Navigate to the Data Services tab.
A data service defines on what compute node a query will run by default along with the means to reach to target data. The latter includes which native driver to use along with settings corresponding to that driver. Multiple Data Sources can reference a single Data Service. The details of any one Data Service are thus maintained in one spot and not spread out all over the place in each Data Source. The latter is a maintenance and support nightmare.
To begin adding a data service click
On the right hand side enter a name and optionally a description.
Set the Compute URL field based on the identified compute node above. Its form is “https://domain:port/dispatch”. A sample is shown below.
Also choose the “Oracle” driver.
The server template is your connecting string.
The server options provide your user name and password information as shown below.
Qarbine’s generic ‘database’ terminology maps to an Oracle ‘schema’. You can limit what schemas and tables are shown in the query tools by specifying a value. Leaving it blank
may show a dropdown list such as that below.
Meanwhile entering “SH”
would show the following dropdown values.
Enable the data service for the desired general user group.
Test your settings by clicking on the toolbar image highlighted below.
The result should be similar to the following.
Save the Data Service by clicking on the image highlighted below.
The data service will be known at the next log on time. Next, see the Oracle query interaction and any tutorial for information on interacting with Oracle from Qarbine.
Sample Data Sets
Oracle provides sample data sets which are described at the following locations
https://github.com/oracle-samples/db-sample-schemas/releases/tag/v23.2
https://docs.oracle.com/en/database/oracle/oracle-database/23/comsc/installing-sample-schemas.html
Schema information can be found at https://docs.oracle.com/en/database/oracle/oracle-database/23/comsc/schema-diagrams.html
References
Details on the features supported by the underlying oracle node module can be found at
https://node-oracledb.readthedocs.io/en/latest/user_guide/appendix_a.html#oracle-database-features-supported-by-node-oracledb